<?php 
class CStatistics extends Zend_Db_Table
{
	protected $_db;
	protected $p_url = '/administrator-statistics-';
	
	public function __construct()
	{
		$this->_db = Zend_Registry::get('db');
	}
	
	/**
	 * 关联区域
	 * @param unknown_type $page
	 * @param unknown_type $per_page
	 * @param unknown_type $params
	 */
	public function unionLocation($page, $per_page, $params, $aname)
	{
		$where = ' where 1=1 ';
		$appendurl = '';
		
		if (!empty ($params ['locationid'])) {
            $where .= ' and ' . $this->_db->quoteInto('s2.location_id=?', $params ['locationid']);
		}
		
		if (!empty ($params ['timestart'])) {
            $where .= ' and ' . $this->_db->quoteInto('s1.add_time>=?', strtotime($params ['timestart']));
		}
		
		if (!empty ($params ['timeend'])) {
            $where .= ' and ' . $this->_db->quoteInto('s1.add_time<=?', strtotime($params ['timeend']));
		}
		
		if (!empty ($params ['city'])) {
            $where .= ' and s2.city like \'%'.$params ['city'].'%\'';
		}
		
		$total = $this->_db->fetchRow ('select count(*) t,sum(s1.money) m from zcshop_orders s1 left join zcshop_order_contacts s2 on s1.contactid=s2.contactid ' . $where);
        return Array ('total' => $total);
	}
	
	/**
	 * 关联用户
	 * @param unknown_type $page
	 * @param unknown_type $per_page
	 * @param unknown_type $params
	 */
	public function unionUser($page, $per_page, $params)
	{
		$where = ' where 1=1 ';
		$appendurl = '';
		
		if (!empty ($params ['email'])) {
            $where .= ' and ' . $this->_db->quoteInto('s2.email=?', $params ['email']);
        	$appendurl .= 'email='.urlencode($params['email']).'&';
		}
		
		if (!empty ($params ['timestart'])) {
            $where .= ' and ' . $this->_db->quoteInto('s1.add_time>=?', strtotime($params ['timestart']));
        	$appendurl .= 'timestart='.urlencode($params['timestart']).'&';
		}
		
		if (!empty ($params ['timeend'])) {
            $where .= ' and ' . $this->_db->quoteInto('s1.add_time<=?', strtotime($params ['timeend']));
        	$appendurl .= 'timeend='.urlencode($params['timeend']).'&';
		}
		
		$total = $this->_db->fetchRow ('SELECT count(*) t,sum(s1.money) m FROM zcshop_orders s1 left join zcshop_members s2 on s1.uid=s2.uid ' . $where);
        
        $pageInfo = $this->multi($total['t'], $per_page, $page, $this->p_url.'member?'.$appendurl);
        
        ($page) ? $page : 1;
        $s = $per_page * ($page - 1);
        
        $sql = 'SELECT s1.* FROM zcshop_orders s1 left join zcshop_members s2 on s1.uid=s2.uid ' . $where . ' limit '.$s.','.$per_page;
        $list     = $this->_db->fetchAll ($sql);
        
        return Array ('page' => $pageInfo, 'list' => $list, 'total' => $total);
	}
	
	/**
	 * 关联用户-排名
	 * @param unknown_type $page
	 * @param unknown_type $per_page
	 * @param unknown_type $params
	 */
	public function unionUserRank($page, $per_page, $params)
	{
		$where = '';
		$appendurl = '';
		
		if (!empty ($params ['orderby'])) {
        	$appendurl .= 'orderby='.urlencode($params['orderby']).'&';
		}
		
		if (!empty ($params ['begintime'])) {
			$appendurl .= 'begintime='.urlencode($params['begintime']).'&';
        	$where .= ' and s1.add_time>='.strtotime($params ['begintime']);
		}
		
		if (!empty ($params ['endtime'])) {
			$appendurl .= 'endtime='.urlencode($params['endtime']).'&';
        	$where .= ' and s1.add_time<='.strtotime($params ['endtime']);
		}
		($page) ? $page : 1;
        $s = $per_page * ($page - 1);
		
        switch ($params['orderby']) {
        	case 'ptime':
        		$sql = 'SELECT count(*) c,s1.uid,s2.email FROM `zcshop_orders` s1 left join `zcshop_members` s2 on s1.uid=s2.uid WHERE s1.`pay_time`>0'.$where.' group by s1.uid order by c desc';
        		$list     = $this->_db->fetchAll ($sql);
        		$total = count($list);
        		$pageInfo = $this->multi($total, $per_page, $page, $this->p_url.'buyrank?'.$appendurl);
        		$sql = 'SELECT count(*) c,s1.uid,s2.email FROM `zcshop_orders` s1 left join `zcshop_members` s2 on s1.uid=s2.uid WHERE s1.`pay_time`>0'.$where.' group by s1.uid order by c desc limit '.$s.','.$per_page;
        		$list     = $this->_db->fetchAll ($sql);
        		break;
        	case 'pmoney':
        		$sql = 'SELECT sum(money) m,s1.uid,s2.email FROM `zcshop_orders` s1 left join `zcshop_members` s2 on s1.uid=s2.uid WHERE s1.`pay_time`>0'.$where.' group by s1.uid order by m desc';
        		$list     = $this->_db->fetchAll ($sql);
        		$total = count($list);
        		$pageInfo = $this->multi($total, $per_page, $page, $this->p_url.'buyrank?'.$appendurl);
        		$sql = 'SELECT sum(money) m,s1.uid,s2.email FROM `zcshop_orders` s1 left join `zcshop_members` s2 on s1.uid=s2.uid WHERE s1.`pay_time`>0'.$where.' group by s1.uid order by m desc limit '.$s.','.$per_page;
        		$list     = $this->_db->fetchAll ($sql);
        		break;
        }
        return Array ('page' => $pageInfo, 'list' => $list);
	}
	
	
    private function multi($num, $perpage, $curpage, $mpurl)
    {
        $multipage = '';
        
        if($num > $perpage)
        {
            $page = 10;
            $offset = 2;
            $pages = ceil($num / $perpage);

            if($page > $pages)
            {
                $from = 1;
                $to = $pages;
            }
            else
            {
                $from = $curpage - $offset;
                $to = $from + $page - 1;

                if($from < 1)
                {
                    $from = 1;
                    $to = $page;
                }
                elseif($to > $pages)
                {
                    $from = $pages - $page + 1;
                    $to = $pages;
                }
            }

            $multipage .= '<a href="'.$mpurl.'page=1&">首页</a> &nbsp;';

            for($i = $from; $i <= $to; $i++)
            {
                if($i != $curpage)
                {
                    $multipage .= '<a href="'.$mpurl.'page='.$i.'&">['.$i.']</a>&nbsp;';
                }
                else
                {
                    $multipage .= '<u><b>['.$i.']</b></u>&nbsp;';
                }
            }

            $multipage .= $pages > $page ? " ... <a href=\"${mpurl}page=$pages&\">[$pages]</a> <a href=\"${mpurl}page=$pages&\">未页</a>" : " <a href=\"${mpurl}page=$pages&\">未页</a>";
		}
		$multipage .= "&nbsp;&nbsp;共有{$num}条记录";
        return $multipage;
    }
    
    public function getOrders($beginTime, $endTime, $flid = 0)
    {
    	$select = $this->_db->select();
    	$select->from('zcshop_orders', '*');
    	
    	if ($beginTime) {
    		$select->where('add_time >= ?', strtotime($beginTime));
    	}
    	
    	if ($endTime) {
    		$select->where('add_time <= ?', strtotime($endTime));
    	}
    	
    	if ($flid) {
    		$select->where('flid = ?', $flid);
    	}
    	
    	$result = $this->_db->fetchAll($select);
    	$return = array();
    	
    	$orderCount = 0;
    	$totalAmount = 0;
    	$memberCount = 0;
    	
    	$memberArray = array();
    	$productArray = array();
    	$counts = 0;
    	foreach ($result as $v) {
    		$orderCount++;
    		$totalAmount += $v['money'];
    		$memberArray[$v['uid']] = 0;
    		$orderArray = unserialize($v['order_string']);
    		
    		if ($orderArray) {    			
    			$patternSize = $orderArray[0]['buy_pattern_size'];
    			$patternSize = preg_replace('/<font style=\'color: red\'>[This size sold out, please wait for 5-7 days]<\/font>,/', '', $patternSize);
    			preg_match_all('/([A-Z]+):(\d+)/', $patternSize, $match);
    			foreach ($orderArray as $value) {
    				for ($i=0; $i<count($match[0]); $i++) {
    					if (isset($productArray[$value['itemid']][$match[1][$i]])) {
    						$productArray[$value['itemid']][$match[1][$i]] += $match[2][$i];
    					} else {
    						$productArray[$value['itemid']][$match[1][$i]] = $match[2][$i];
    					}
    					$counts += $match[2][$i];
    				}
    			}
    		}
    		
    		//print_r($orderArray);
    	}  

    	$return['order_count'] = $orderCount;
    	$return['total_amount'] = $totalAmount;
    	$return['member_count'] = count($memberArray);
    	
    	$memberArray = array_keys($memberArray);
    	$memberAnoCount = 0;
    	if ($memberArray) {
    		$memberResult = $this->_db->fetchAll('select count(*) as counts from zcshop_members where username="" and uid in (' .implode(',', $memberArray). ')');
    		$memberAnoCount = $memberResult[0]['counts'];
    	}
    	$return['member_ano_count'] = $memberAnoCount;
    	$return['product'] = $productArray;
    	$return['counts'] = $counts;
    	return $return;
    }
	
    public function orderPage($pageCount, $pageNum, $page, $param)
    {
    	return $this->multi($pageCount, $pageNum, $page, $param);
    }
    
    public function membermonth($page, $per_page, $params) {
    	$where = ' where 1=1 ';
		$appendurl = '';
		
		if (!empty ($params ['timestart'])) {
            $where .= ' and ' . $this->_db->quoteInto('o.`add_time`>=?', strtotime($params ['timestart']));
		}
		
		if (!empty ($params ['timeend'])) {
            $where .= ' and ' . $this->_db->quoteInto('o.`add_time`<=?', strtotime($params ['timeend']));
		}
		
    	$sql = 'SELECT o.`add_time`,o.`orderid`,o.`money`,o.`order_string`,o.`orderid`,c.`post_code`,c.`city`,c.`name`,c.`phone`,l.`flname`,m.`email` FROM `zcshop_orders`  o left join `zcshop_order_contacts` c on o.`contactid`=c.`contactid` left join `zcshop_members` m on  o.`uid`=m.`uid` left join `zcshop_locationfls` l on l.`flid`=c.`location_id` '.$where;
    	$list = $this->_db->fetchAll ($sql);
        return Array ('list' => $list);
    }
	
	public static function sortBySellNum($a,$b){
		$aNum = 0;
		foreach($a as $key => $v){
			$aNum+=$v;
		}
		$bNum = 0;
		foreach($b as $key => $v){
			$bNum+=$v;
		}
		return $bNum - $aNum;
	}
}